SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `dss_lms` ;
CREATE SCHEMA IF NOT EXISTS `dss_lms` DEFAULT CHARACTER SET latin1 ;
SHOW WARNINGS;
USE `dss_lms` ;

-- -----------------------------------------------------
-- Table `dss_lms`.`location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`location` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`location` (
  `LocationID` DECIMAL(10,0) NOT NULL ,
  `LocationCode` VARCHAR(10) NULL DEFAULT NULL ,
  `Name` VARCHAR(45) NULL DEFAULT NULL ,
  `Category` VARCHAR(45) NULL DEFAULT NULL ,
  `Address` VARCHAR(45) NULL DEFAULT NULL ,
  `Tehsil/Block` VARCHAR(45) NULL DEFAULT NULL ,
  `District` VARCHAR(45) NULL DEFAULT NULL ,
  `State` VARCHAR(20) NULL DEFAULT NULL ,
  `Country` VARCHAR(20) NULL DEFAULT NULL ,
  `LandArea` DECIMAL(10,0) NULL DEFAULT NULL COMMENT 'LandArea: Square Feet' ,
  `LandTerm` VARCHAR(45) NULL DEFAULT NULL ,
  `LandType` VARCHAR(45) NULL DEFAULT NULL ,
  `RunningFeet` DECIMAL(10,0) NULL DEFAULT NULL ,
  `CoveredArea` DECIMAL(10,0) NULL DEFAULT NULL ,
  `RegisteredOnName` VARCHAR(20) NULL DEFAULT NULL ,
  `RegistrationNo` DECIMAL(10,0) NULL DEFAULT NULL ,
  `RegistrationDate` DATE NULL DEFAULT NULL ,
  `SubRegistrar` VARCHAR(20) NULL DEFAULT NULL ,
  `EstimatedValue` DOUBLE NULL DEFAULT NULL ,
  `StampExpenditure` DOUBLE NULL DEFAULT NULL ,
  `OtherExpenditure` DOUBLE NULL DEFAULT NULL ,
  `RegistryHolder` VARCHAR(20) NULL DEFAULT NULL ,
  `RegistryCopy` VARCHAR(45) NULL DEFAULT NULL ,
  `BuildingMap` VARCHAR(45) NULL DEFAULT NULL ,
  `Canteen` VARCHAR(5) NULL DEFAULT NULL ,
  `Landmark` VARCHAR(20) NULL DEFAULT NULL ,
  `DistanceFromMainRoad` DECIMAL(10,0) NULL DEFAULT NULL ,
  `ResponsiblePerson` VARCHAR(20) NULL DEFAULT NULL ,
  `Status` VARCHAR(10) NULL DEFAULT NULL ,
  `TotalBalance` DOUBLE NULL DEFAULT NULL ,
  PRIMARY KEY (`LocationID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`role` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`role` (
  `RoleID` DECIMAL(10,0) NOT NULL ,
  `RoleDescription` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`RoleID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`user` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`user` (
  `UserID` DECIMAL(10,0) NOT NULL ,
  `JameInsanID` DECIMAL(20,0) NULL DEFAULT NULL ,
  `InsanID (Locket Number)` DECIMAL(20,0) NULL DEFAULT NULL ,
  `Name` VARCHAR(20) NULL DEFAULT NULL ,
  `Adress` VARCHAR(45) NULL DEFAULT NULL ,
  `Tehsil/Block` VARCHAR(45) NULL DEFAULT NULL ,
  `District` VARCHAR(45) NULL DEFAULT NULL ,
  `State` VARCHAR(20) NULL DEFAULT NULL ,
  `Country` VARCHAR(20) NULL DEFAULT NULL ,
  `Contact Number` DECIMAL(10,0) NULL DEFAULT NULL ,
  `RoleID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Password` VARCHAR(45) NULL DEFAULT NULL ,
  `Status` VARCHAR(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`UserID`) ,
  CONSTRAINT `Role ID`
    FOREIGN KEY (`RoleID` )
    REFERENCES `dss_lms`.`role` (`RoleID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX `Role ID` ON `dss_lms`.`user` (`RoleID` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`account` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`account` (
  `TransactionID` DECIMAL(10,0) NOT NULL ,
  `FormID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `LocationID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Amount` DOUBLE NULL DEFAULT NULL ,
  `Reason` VARCHAR(45) NULL DEFAULT NULL ,
  `ItemPurchased` VARCHAR(45) NULL DEFAULT NULL ,
  `TransactionDate` DATE NULL DEFAULT NULL ,
  `ReturnDate` DATE NULL DEFAULT NULL ,
  `InterestAmount` DOUBLE NULL DEFAULT NULL ,
  `PersonID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Narration` VARCHAR(45) NULL DEFAULT NULL ,
  `TransactionType` VARCHAR(10) NULL DEFAULT NULL ,
  `DateTimerStamp` DATETIME NULL DEFAULT NULL ,
  `ModeOfPayment` VARCHAR(20) NULL DEFAULT NULL ,
  `TransactionAuthorizerID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Status` VARCHAR(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`TransactionID`) ,
  CONSTRAINT `Location  ID`
    FOREIGN KEY (`LocationID` )
    REFERENCES `dss_lms`.`location` (`LocationID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `TransactionAuthorizerID`
    FOREIGN KEY (`TransactionAuthorizerID` )
    REFERENCES `dss_lms`.`user` (`UserID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX `Location  ID` ON `dss_lms`.`account` (`LocationID` ASC) ;

SHOW WARNINGS;
CREATE INDEX `TransactionAuthorizerID` ON `dss_lms`.`account` (`TransactionAuthorizerID` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`image`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`image` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`image` (
  `ImageID` DECIMAL(10,0) NOT NULL ,
  `LocationID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Path` VARCHAR(45) NULL DEFAULT NULL ,
  `Size` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`ImageID`) ,
  CONSTRAINT `Location_ID`
    FOREIGN KEY (`LocationID` )
    REFERENCES `dss_lms`.`location` (`LocationID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX `Location_ID` ON `dss_lms`.`image` (`LocationID` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`location_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`location_user` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`location_user` (
  `UserID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `LocationID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Designation` VARCHAR(45) NULL DEFAULT NULL ,
  `Status` VARCHAR(20) NULL DEFAULT NULL ,
  CONSTRAINT `Loc  ID`
    FOREIGN KEY (`LocationID` )
    REFERENCES `dss_lms`.`location` (`LocationID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Usr_ID`
    FOREIGN KEY (`UserID` )
    REFERENCES `dss_lms`.`user` (`UserID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX `Loc  ID` ON `dss_lms`.`location_user` (`LocationID` ASC) ;

SHOW WARNINGS;
CREATE INDEX `Usr_ID` ON `dss_lms`.`location_user` (`UserID` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`user_role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`user_role` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`user_role` (
  `UserID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `RoleID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `StartDate` DATE NULL DEFAULT NULL ,
  `EndDate` DATE NULL DEFAULT NULL ,
  `Status` VARCHAR(20) NULL DEFAULT NULL ,
  CONSTRAINT `User  ID`
    FOREIGN KEY (`UserID` )
    REFERENCES `dss_lms`.`user` (`UserID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Role  ID`
    FOREIGN KEY (`RoleID` )
    REFERENCES `dss_lms`.`role` (`RoleID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX `User  ID` ON `dss_lms`.`user_role` (`UserID` ASC) ;

SHOW WARNINGS;
CREATE INDEX `Role  ID` ON `dss_lms`.`user_role` (`RoleID` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `dss_lms`.`video`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dss_lms`.`video` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `dss_lms`.`video` (
  `VideoID` DECIMAL(10,0) NOT NULL ,
  `LocationID` DECIMAL(10,0) NULL DEFAULT NULL ,
  `Path` VARCHAR(45) NULL DEFAULT NULL ,
  `Size` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`VideoID`) ,
  CONSTRAINT `Location ID`
    FOREIGN KEY (`LocationID` )
    REFERENCES `dss_lms`.`location` (`LocationID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX `Location ID` ON `dss_lms`.`video` (`LocationID` ASC) ;

SHOW WARNINGS;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
